using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using DataTransferObject;
using DataAccess;

namespace DataAccess
{
    public class clsNhanVienDAO : clsDataAccessHelper
    {
        // Fields
        private String tenTable;
        DataTable tbl=new DataTable ();

        // Contructors
        public clsNhanVienDAO()
        {
            this.tenTable = "NHANVIEN";
        }
        #region Methods
        //-------------------------------------Lấy dữ liệu bảng-----------------------

        //Phương thức kiểm tra tên đăng nhập.
        public bool KiemTraTenDangNhap(String TenDangNhap)
        {
            String sql;
            DataTable dt;
            try
            {
                sql = "SELECT MANV FROM " + this.tenTable;
                sql += " WHERE TENDANGNHAP='" + TenDangNhap + "'";
                dt = new DataTable();
                dt = this.ExecuteQuery(sql);

                if (dt.Rows.Count > 0)
                    return true;
                return false;
            }
            catch
            {
                return false;
            }
        }

        //Phương thức kiểm tra đăng nhập.
        public bool KiemTraDangNhap(String TenDangNhap, String MatKhau)
        {
            String sql;
            DataTable dt;
            try
            {
                sql = "SELECT MANV,HOTEN,TENDANGNHAP FROM " + this.tenTable;
                sql += " WHERE TENDANGNHAP='" + TenDangNhap + "'";
                sql += " AND MATKHAU='" + MatKhau + "'";
                dt = new DataTable();
                dt = this.ExecuteQuery(sql);

                if (dt.Rows.Count > 0)
                    return true;
                return false;
            }
            catch
            {
                return false;
            }
        }

        //Phương thức kiểm tra tài khoản.
        public clsNhanVienDTO KiemTraTaiKhoan(String TenDangNhap, String MatKhau, String MaBoPhan)
        {
            clsNhanVienDTO NhanVienDTO = null;
            Object[] obj = new Object[3];

            String sql;
            DataTable dt;
            try
            {
                sql = "SELECT MANV,HOTEN,TENDANGNHAP FROM " + this.tenTable;
                sql += " N,BOPHAN B";
                sql += " WHERE TENDANGNHAP='" + TenDangNhap + "'";
                sql += " AND MATKHAU='" + MatKhau + "'";
                sql += " AND N.MABP='" + MaBoPhan + "'";
                sql += " AND N.MABP=B.MABP";
                dt = new DataTable();
                dt = this.ExecuteQuery(sql);

                obj = dt.Rows[0].ItemArray;
                NhanVienDTO = new clsNhanVienDTO();
                NhanVienDTO.MaNV = obj[0].ToString();
                NhanVienDTO.HoTen = obj[1].ToString();
                NhanVienDTO.TenDangNhap = obj[2].ToString();

                return NhanVienDTO;
            }
            catch
            {
                return null;
            }
        }

        public DataTable SelectAll()
        {
            String sql;
            DataTable dt;
            try
            {
                sql = "SELECT * FROM " + this.tenTable;
                dt = new DataTable();
                dt = this.ExecuteQuery(sql);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //-----------------------------------------------------------------------------//


        public DataTable LayThongTinNhanVien()
        {
            String sql;
            try
            {
                sql = "select MaNV,HoTen,TenDangNhap,MatKhau,NgaySinh,TenCV,TenBP,TenBC from NhanVien,ChucVu,BoPhan,BangCap where NhanVien.MaCV=ChucVu.MaCV and NhanVien.MaBP=BoPhan.MaBP and NhanVien.MaBC=BangCap.MaBC";
                tbl = this.ExecuteQuery(sql);
               
                return tbl;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public DataTable TimKiemNhanVien(clsNhanVienDTO obj)
        {
            string sql;
            try
            {
                sql ="select MaNV,HoTen,GioiTinh,DiaChi,NgaySinh,DienThoai,TenCV,TenDangNhap,MatKhau,HinhAnh,TenBP,TenBC from NhanVien,ChucVu,BoPhan,BangCap where NhanVien.MaCV=ChucVu.MaCV and NhanVien.MaBP=BoPhan.MaBP and NhanVien.MaBC=BangCap.MaBC and MaNV='" + obj.MaNV + "'";
                tbl = this.ExecuteQuery(sql);
                return tbl;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //-------------------------------------Thêm vào bảng-----------------------

        public bool Insert(clsNhanVienDTO obj)
        {
            String sql;
            bool check = false;

            try
            {
                sql = "Set dateformat dmy INSERT INTO " + this.tenTable;
                sql += "(MANV,HOTEN,GIOITINH,DIACHI,NGAYSINH,HINHANH,TENDANGNHAP,MATKHAU,DIENTHOAI,MABC,MACV,MABP) values('";
                sql += obj.MaNV + "',N'";
                sql += obj.HoTen + "',N'";
                sql += obj.GioiTinh + "',N'";
                sql += obj.DiaChi + "','";
                sql += obj.NgaySinh.Day + "/" + obj.NgaySinh.Month + "/" + obj.NgaySinh.Year + "',N'";
                sql += obj.HinhAnh + "','";
                sql += obj.TenDangNhap + "','";
                sql += obj.MatKhau + "','";
                sql += obj.DienThoai + "','";
                sql += obj.MaBC + "','";
                sql += obj.MaCV + "','";
                sql += obj.MaBP + "')";
                check = this.ExecuteNonQuery(sql);

                return check;
            }
            catch
            {
                return check;
            }
        }

        //-----------------------------------------------------------------------------//



        //-------------------------------------Xóa Bảng-----------------------
        public bool Delete(clsNhanVienDTO obj)
        {
            String sql;
            bool check = false;

            try
            {
                sql = "DELETE ";
                sql += " FROM " + this.tenTable;
                sql += " WHERE MANV LIKE '" + obj.MaNV + "'";
                check = this.ExecuteNonQuery(sql);

                return check;
            }
            catch
            {
                return check;
            }
        }



        //-----------------------------------------------------------------------------//



        //-------------------------------------Sửa vào bảng-----------------------

        public bool Update(clsNhanVienDTO obj)
        {
            String sql;
            bool check = false;

            try
            {
                sql = "Set dateformat dmy UPDATE " + this.tenTable;
                sql += " SET";
                sql += " HOTEN = N'";
                sql += obj.HoTen + "',";
                sql += " GIOITINH = N'";
                sql += obj.GioiTinh + "',";
                sql += " DIACHI = N'";
                sql += obj.DiaChi + "',";
                sql += " NGAYSINH = '";
                sql += obj.NgaySinh.Day + "/" + obj.NgaySinh.Month + "/" + obj.NgaySinh.Year + "',";
                sql += " HINHANH = N'";
                sql += obj.HinhAnh + "',";
                sql += " TENDANGNHAP = '";
                sql += obj.TenDangNhap + "',";
                sql += " MATKHAU = '";
                sql += obj.MatKhau + "',";
                sql += " DIENTHOAI = '";
                sql += obj.DienThoai + "',";
                sql += " MABC = '";
                sql += obj.MaBC + "',";
                sql += " MACV = '";
                sql += obj.MaCV + "',";
                sql += " MABP = '";
                sql += obj.MaBP + "'";
                sql += " WHERE MANV LIKE '";
                sql += obj.MaNV + "'";
                check = this.ExecuteNonQuery(sql);

                return check;
            }
            catch
            {
                return check;
            }
        }


        //-----------------------------------------------------------------------------//
        //Đăng nhập.
        //public bool Login(clsNhanVienDTO obj)
        //{

        //}

        //

        #endregion
    }
}